R Notebook

library(readxl)
library(tidyverse)

-- Attaching packages --------------------------------------- tidyverse 1.2.1 --
v ggplot2 3.0.0     v purrr   0.2.5
v tibble  1.4.2     v dplyr   0.7.6
v tidyr   0.8.1     v stringr 1.3.1
v readr   1.1.1     v forcats 0.3.0
-- Conflicts ------------------------------------------ tidyverse_conflicts() --
x dplyr::filter() masks stats::filter()
x dplyr::first()  masks xts::first()
x dplyr::lag()    masks stats::lag()
x dplyr::last()   masks xts::last()

Despesas por função (aqui!)

loadfonts()

No regular (non-bold, non-italic) version of Aharoni. Skipping setup for this font.
Algerian already registered with pdfFonts().
Andalus already registered with pdfFonts().
Angsana New already registered with pdfFonts().
AngsanaUPC already registered with pdfFonts().
Aparajita already registered with pdfFonts().
Arabic Typesetting already registered with pdfFonts().
Arial Black already registered with pdfFonts().
Arial already registered with pdfFonts().
Arial Narrow already registered with pdfFonts().
Arial Unicode MS already registered with pdfFonts().
Baskerville Old Face already registered with pdfFonts().
Bauhaus 93 already registered with pdfFonts().
Bell MT already registered with pdfFonts().
Berlin Sans FB already registered with pdfFonts().
No regular (non-bold, non-italic) version of Berlin Sans FB Demi. Skipping setup for this font.
Bernard MT Condensed already registered with pdfFonts().
Bodoni MT Poster Compressed already registered with pdfFonts().
Book Antiqua already registered with pdfFonts().
Bookman Old Style already registered with pdfFonts().
Bookshelf Symbol 7 already registered with pdfFonts().
Bradley Hand ITC already registered with pdfFonts().
Britannic Bold already registered with pdfFonts().
Broadway already registered with pdfFonts().
Browallia New already registered with pdfFonts().
BrowalliaUPC already registered with pdfFonts().
No regular (non-bold, non-italic) version of Brush Script MT. Skipping setup for this font.
Calibri already registered with pdfFonts().
Calibri Light already registered with pdfFonts().
Californian FB already registered with pdfFonts().
No regular (non-bold, non-italic) version of Cambria. Skipping setup for this font.
Candara already registered with pdfFonts().
Centaur already registered with pdfFonts().
Century already registered with pdfFonts().
Century Gothic already registered with pdfFonts().
Chiller already registered with pdfFonts().
Colonna MT already registered with pdfFonts().
Comic Sans MS already registered with pdfFonts().
Consolas already registered with pdfFonts().
Constantia already registered with pdfFonts().
Cooper Black already registered with pdfFonts().
Corbel already registered with pdfFonts().
Cordia New already registered with pdfFonts().
CordiaUPC already registered with pdfFonts().
Courier New already registered with pdfFonts().
DaunPenh already registered with pdfFonts().
David already registered with pdfFonts().
DFKai-SB already registered with pdfFonts().
DilleniaUPC already registered with pdfFonts().
DokChampa already registered with pdfFonts().
Ebrima already registered with pdfFonts().
Estrangelo Edessa already registered with pdfFonts().
EucrosiaUPC already registered with pdfFonts().
Euphemia already registered with pdfFonts().
FangSong already registered with pdfFonts().
Footlight MT Light already registered with pdfFonts().
Franklin Gothic Medium already registered with pdfFonts().
FrankRuehl already registered with pdfFonts().
FreesiaUPC already registered with pdfFonts().
Freestyle Script already registered with pdfFonts().
French Script MT already registered with pdfFonts().
Gabriola already registered with pdfFonts().
Garamond already registered with pdfFonts().
Gautami already registered with pdfFonts().
Georgia already registered with pdfFonts().
Gisha already registered with pdfFonts().
No regular (non-bold, non-italic) version of Harlow Solid Italic. Skipping setup for this font.
Harrington already registered with pdfFonts().
High Tower Text already registered with pdfFonts().
Impact already registered with pdfFonts().
Informal Roman already registered with pdfFonts().
IrisUPC already registered with pdfFonts().
Iskoola Pota already registered with pdfFonts().
JasmineUPC already registered with pdfFonts().
Jokerman already registered with pdfFonts().
Juice ITC already registered with pdfFonts().
KaiTi already registered with pdfFonts().
Kalinga already registered with pdfFonts().
Kartika already registered with pdfFonts().
Khmer UI already registered with pdfFonts().
KodchiangUPC already registered with pdfFonts().
Kokila already registered with pdfFonts().
Kristen ITC already registered with pdfFonts().
Kunstler Script already registered with pdfFonts().
Lao UI already registered with pdfFonts().
Latha already registered with pdfFonts().
Wide Latin already registered with pdfFonts().
Leelawadee already registered with pdfFonts().
Levenim MT already registered with pdfFonts().
LilyUPC already registered with pdfFonts().
More than one version of regular/bold/italic found for Lucida Bright. Skipping setup for this font.
No regular (non-bold, non-italic) version of Lucida Calligraphy. Skipping setup for this font.
Lucida Console already registered with pdfFonts().
More than one version of regular/bold/italic found for Lucida Fax. Skipping setup for this font.
No regular (non-bold, non-italic) version of Lucida Handwriting. Skipping setup for this font.
Lucida Sans Unicode already registered with pdfFonts().
No regular (non-bold, non-italic) version of Magneto. Skipping setup for this font.
Malgun Gothic already registered with pdfFonts().
Mangal already registered with pdfFonts().
Marlett already registered with pdfFonts().
Matura MT Script Capitals already registered with pdfFonts().
Microsoft Yi Baiti already registered with pdfFonts().
Microsoft Himalaya already registered with pdfFonts().
Microsoft JhengHei already registered with pdfFonts().
Microsoft New Tai Lue already registered with pdfFonts().
Microsoft PhagsPa already registered with pdfFonts().
Microsoft Sans Serif already registered with pdfFonts().
Microsoft Tai Le already registered with pdfFonts().
Microsoft Uighur already registered with pdfFonts().
Microsoft YaHei already registered with pdfFonts().
Miriam already registered with pdfFonts().
Miriam Fixed already registered with pdfFonts().
Mistral already registered with pdfFonts().
Modern No. 20 already registered with pdfFonts().
Mongolian Baiti already registered with pdfFonts().
Monotype Corsiva already registered with pdfFonts().
MoolBoran already registered with pdfFonts().
MS Reference Sans Serif already registered with pdfFonts().
MS Reference Specialty already registered with pdfFonts().
MV Boli already registered with pdfFonts().
Narkisim already registered with pdfFonts().
Niagara Engraved already registered with pdfFonts().
Niagara Solid already registered with pdfFonts().
Nyala already registered with pdfFonts().
Old English Text MT already registered with pdfFonts().
Onyx already registered with pdfFonts().
Palatino Linotype already registered with pdfFonts().
Papyrus already registered with pdfFonts().
Parchment already registered with pdfFonts().
Plantagenet Cherokee already registered with pdfFonts().
Playbill already registered with pdfFonts().
Poor Richard already registered with pdfFonts().
Pristina already registered with pdfFonts().
More than one version of regular/bold/italic found for Raavi. Skipping setup for this font.
Ravie already registered with pdfFonts().
Rod already registered with pdfFonts().
Sakkal Majalla already registered with pdfFonts().
Segoe Print already registered with pdfFonts().
Segoe Script already registered with pdfFonts().
Segoe UI already registered with pdfFonts().
Segoe UI Light already registered with pdfFonts().
Segoe UI Semibold already registered with pdfFonts().
Segoe UI Symbol already registered with pdfFonts().
Shonar Bangla already registered with pdfFonts().
Showcard Gothic already registered with pdfFonts().
Shruti already registered with pdfFonts().
SimHei already registered with pdfFonts().
Simplified Arabic already registered with pdfFonts().
Simplified Arabic Fixed already registered with pdfFonts().
SimSun-ExtB already registered with pdfFonts().
Snap ITC already registered with pdfFonts().
Source Sans Pro Black already registered with pdfFonts().
Source Sans Pro already registered with pdfFonts().
Source Sans Pro ExtraLight already registered with pdfFonts().
Source Sans Pro Light already registered with pdfFonts().
Source Sans Pro SemiBold already registered with pdfFonts().
Stencil already registered with pdfFonts().
Sylfaen already registered with pdfFonts().
Symbol already registered with pdfFonts().
Tahoma already registered with pdfFonts().
Tempus Sans ITC already registered with pdfFonts().
Times New Roman already registered with pdfFonts().
Traditional Arabic already registered with pdfFonts().
Trebuchet MS already registered with pdfFonts().
Tunga already registered with pdfFonts().
Utsaah already registered with pdfFonts().
Vani already registered with pdfFonts().
Verdana already registered with pdfFonts().
Vijaya already registered with pdfFonts().
Viner Hand ITC already registered with pdfFonts().
No regular (non-bold, non-italic) version of Vivaldi. Skipping setup for this font.
Vladimir Script already registered with pdfFonts().
Vrinda already registered with pdfFonts().
Webdings already registered with pdfFonts().
Wingdings already registered with pdfFonts().
Wingdings 2 already registered with pdfFonts().
Wingdings 3 already registered with pdfFonts().

library(ipeaData)
pibs <- ipeadata("BM12_PIBAC12")

[1] "NA. 01/1990 a 09/2017. Acesso em: 09/07/2018"

pibs <- pibs[MES=="12" & ANO %in% (2002:2017)]$VALVALOR
## incorpora na mão pib de 2017 que ainda não estava disponível no pacote ipeaData. Peguei do IBGE.
pibs <- c(pibs, 6559940)
for (i in 1:length(pibs)){
  sh_fun[,i+4] <- round(sh_fun[,i+4] / pibs[i], 4)
}
# passando para um formato tidy
sh_fun <- sh_fun %>%
  gather(5:20, key="Ano", value="Valor")
# funcoes para plotar os gráficos de barra
plot_funcoes <- function(funcao){
  ggplot(
      sh_fun %>% filter(Funcao_Maius==funcao & ente != "Consolidado"), 
      aes(
        fill = factor(ente, levels = rev(c("União", "Estados", "Municípios"))), 
        y = Valor, 
        x = Ano)) +
    #coord_flip() +
    geom_bar(stat = "identity", width=0.7, color = "white", size = 1) + #position = "fill"
    #geom_area(aes(group = ente),stat = "identity", position = "stack", color = "white") + #position = "fill"
    labs(
      x = NULL,
      y = NULL,
      fill = "Ente",
      title = paste("Distribuição das despesas com ", tolower(funcao), " por ente da Federação ","2002\u2013","2017", sep=""),
      subtitle = "Em percentuais do PIB."
    ) +
    scale_y_continuous(labels = scales::percent) +
    scale_fill_manual(values = tres_cores) +
    geom_text(aes(label= scales::percent(Valor)),size = 3, position = position_stack(vjust = 0.5), hjust = 0.5, family = "Source Sans Pro", color = "white") +
    tema_BSPN()
}
funcoes_de_interesse <- c("EDUCAÇÃO", "SAÚDE", "ASSISTÊNCIA SOCIAL", "SEGURANÇA PÚBLICA")
lapply(funcoes_de_interesse, plot_funcoes)

[[1]]

[[2]]

[[3]]

[[4]]

plot_funcoes("EDUCAÇÃO")

plot_funcoes("SAÚDE")

plot_funcoes("ASSISTÊNCIA SOCIAL")

plot_funcoes("SEGURANÇA PÚBLICA")

#ggplot(serie_f %>% filter(Funcao=="EDUCAÇÃO"), aes(x = Ano, y = Valor)) +
  #geom_point(aes(color=ente))+
  #geom_line(aes(group=ente))
  #geom_area(aes(fill = ente, group 

Todas as funções

Os ativos

library(plotly)

Attaching package: 㤼㸱plotly㤼㸲

The following object is masked from 㤼㸱package:ggplot2㤼㸲:

    last_plot

The following object is masked from 㤼㸱package:stats㤼㸲:

    filter

The following object is masked from 㤼㸱package:graphics㤼㸲:

    layout

# le os dados
ativo <- read.csv2("links-ativo.csv")
ativo$valor <- as.numeric(gsub("[.]", "", ativo$valor))
passivo <- read.csv2("links-passivo.csv")
passivo$valor <- as.numeric(gsub("[.]", "", passivo$valor))
# cores
mtx_tres_cores <- col2rgb(tres_cores_pasteis)
cores <- NULL
for (i in 1:dim(mtx_tres_cores)[2] ) {
  cores <- c(cores, paste("rgba(", mtx_tres_cores[1,i],", ",  mtx_tres_cores[2,i],", ",  mtx_tres_cores[3,i],", 0.7)", sep = ""))
}
tbl_cores <- data.frame("ente" = levels(ativo$ente),cores)
# funcao de plotagem
plota_sankey <- function(dados, rotulos, cor_nos){
  plot_ly(
    type = "sankey",
    orientation = "h",
    opacity = 0.6,
    width = 800,
    height = 600,
    textfont = list(
      family = "Source Sans Pro",
      color = "#444444",
      size = 12),
    
    node = list(
      label = rotulos,
      color = cor_nos, ##004a93
      pad = 10,
      thickness = 25,
      line = list(
        color = cor_nos,
        width = 0)),
    
    hoverlabel = list(
      font = list(
        family = "Source Sans Pro")),
    
    link = list(
      source = dados$src,
      target = dados$trg,
      value =  dados$valor,
      color =  dados$cores)) %>% 
  
    layout(
      title = "",
      font = list(
        family = "Source Sans Pro",
        size = 11,
        color = "#004a93"))
}
# funcao para processar os dados e chamar a função de plotagem
##### incluir argumento na função para cor dos nós!
plota_ente <- function(ente_in, dados, cor_nos){
  
  if (ente_in == "Consolidado") {
    dados_ente <- dados}
    else {dado_ente <- dados %>% filter(ente == ente_in)
  }
  
  lista_nos <- unique(c(levels(dados_ente$up), levels(dados_ente$down)))
  numeros_nos <- 0:(length(lista_nos)-1)
  nos <- data.frame(lista_nos, numeros_nos)
  
  matriz <- dados_ente %>%
    left_join(nos, by = c("up" = "lista_nos")) %>%
    rename(src = numeros_nos) %>%
    left_join(nos, by = c("down" = "lista_nos")) %>%
    rename(trg = numeros_nos)
  
  matriz <- matriz %>%
    left_join(tbl_cores)
  
  plota_sankey(matriz, lista_nos, cor_nos)
  #return(matriz)
}
#dados_consolidado <- gera_dados_ente("Consolidado")
#dados_Uniao <- gera_dados_ente("União")
#dados_Estados <- gera_dados_ente("Estados")
#dados_Municipios <- gera_dados_ente("Municípios")
plota_ente("Consolidado", ativo, "#444444")

Column `up`/`lista_nos` joining factors with different levels, coercing to character vectorColumn `down`/`lista_nos` joining factors with different levels, coercing to character vectorJoining, by = "ente"

plota_ente("Consolidado", passivo, "#333333")

Column `up`/`lista_nos` joining factors with different levels, coercing to character vectorColumn `down`/`lista_nos` joining factors with different levels, coercing to character vectorJoining, by = "ente"

Diagrama Consolidado